﻿const express = require(`express`)
const router = express.Router()
const db=require('../db/db')

router.get('/office_card',function (req,res,next) {
    let sql=`select us.username,us.status,us.userid,us.admin_id,olt.start_time,ofp.start_time ofp_start_time,ofp.field_personnel_place,ofp.field_personnel_cause,ha.hr_attend_startWork,ha.hr_attend_knockOff
    from uc_user us
             left join (select a.userid, b.start_time,b.createdate
                        from uc_user a,
                             own_leave_table b
                        where a.username = b.applicant
                          and substr(b.start_time, 1, 10) = DATE_FORMAT(now(), '%Y-%m-%d')) olt
                       on us.userid = olt.userid
             left join (select c.username,d.field_personnel_place,d.field_personnel_cause,d.start_time
                        from uc_user c,
                             own_field_personnel d
                        where c.userid = d.field_personnel_userid
                          and substr(d.start_time, 1, 10) = DATE_FORMAT(now(), '%Y-%m-%d')) ofp
                                    on us.username = ofp.username
                    left join(select e.userid,f.hr_attend_startWork,f.hr_attend_knockOff,f.hr_attend_userId from uc_user e,hr_attend f where e.userid=f.hr_attend_userId and f.hr_attend_date=DATE_FORMAT(NOW(), '%Y-%m-%d'))ha
                                    on us.userid = ha.hr_attend_userId
    where us.admin_id <> 0 and us.status <>2 and us.username <> '任武' and us.username <> '李新华' and us.username <> '任奕凝' and us.username <> '总裁'order by olt.createdate desc ,ofp.start_time desc ;`
      db.query(sql,[],function(results,fields){
        res.json({
          code:200,
          data:results    
      })
      next()
      })
})

router.get('/each_project',(req,res,next)=>{
  let sql=`select c.constuct_project_dep_name,b.construct_project_name,
  substring_index(c.constuct_project_dep_leader, '/', 1) constuct_project_dep_leader,
  count(1) total 
from hr_attend a
    inner join construct_project_table b on a.hr_attend_projectId = b.construct_project_id
    inner join construct_project_dep c on b.construct_project_dep = c.constuct_project_dep_id
where a.hr_attend_projectId is not null
and a.hr_attend_date = date_format(now(), '%Y-%m-%d')
group by c.constuct_project_dep_id;`
db.query(sql,[],function(results,fields){
  res.json({
    code:200,
    data:results    
})
next()
})
})


module.exports = router